package chat.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.mchange.v2.c3p0.ComboPooledDataSource;


import chat.user.domain.User;

public class UserDao
{
	User user = new User();
	ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	QueryRunner qr = new QueryRunner(dataSource);
	
	public User findByName(String username)
	{
		try 
		{
			String sql = "select * from user where username=?";
			User user = qr.query(sql, new BeanHandler<User>(User.class),username);
			if(user == null)
			{
				return null;
			}
			else
			{
				return user;
			}
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
		
	}

	public void addUser(User form) {
		try 
		{
			Connection conn = dataSource.getConnection();
			String sql = "insert into user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			qr.update(conn, sql, form.getId(),form.getUsername(),form.getPassword(),
					form.getBirthday(),form.getSex(),form.getEmail(),
					form.getPhone(),form.getNote(),form.getStatus(),
					form.getMode(),form.getCreatetime(),form.getUpdatetime(),
					form.getUpdateby(),form.getLastupdatetime(),form.getIsonline());
			
			
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}

	public void edit(User user,String sb,String value) {
		// TODO Auto-generated method stub
		try 
		{
			Connection conn = dataSource.getConnection();
			String sql = "update user set "+sb+"=?,updatetime=?,updateby=? where id=? ";
			qr.update(conn, sql, value,user.getUpdatetime(),user.getUpdateby(),user.getId());
//			String sql = "update user set username=? where id=? ";
//			qr.update(conn, sql, user.getUsername(),user.getId());
			
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}

	public User findById(int id) {
		try 
		{
			String sql = "select * from user where id=?";
			User user = qr.query(sql, new BeanHandler<User>(User.class),id);
			if(user == null)
			{
				return null;
			}
			else
			{
				return user;
			}
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}

	public List<User> findAll() {
		try 
		{
			String sql = "select * from user";
			List<User> userList = qr.query(sql, new BeanListHandler<User>(User.class));
			return userList;
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}


//	public void updatelasttime(int userId,String strDate) {
//		try 
//		{
//			Connection conn = dataSource.getConnection();
//			String sql = "update user set lastupdatetime=? where id=?";
//			qr.update(conn, sql, strDate,userId);
//			
//		} catch (SQLException e) 
//		{
//			throw new RuntimeException(e);
//		}
//	}
//	
//	public List<String> findlasttime() {
//		try 
//		{
//			
//			
//			List<String> list = new ArrayList<String>();
//			Connection conn = dataSource.getConnection();
//			String sql = "select lastupdatetime from user";
//			PreparedStatement pre =  conn.prepareStatement(sql);
//			ResultSet res = pre.executeQuery();
//			while(res.next())
//			{
//				
//				list.add(res.getString("lastupdatetime"));
//			}
//			return list;
//			
//		} catch (SQLException e) 
//		{
//			throw new RuntimeException(e);
//		}
//	}
//	public int shicha(String lastupdatetime,String strDate) {
//		try 
//		{
//			String sql = "select UNIX_TIMESTAMP(?)-UNIX_TIMESTAMP(?)";
//			
//			Number num = (Number)qr.query(sql, new ScalarHandler(),strDate,lastupdatetime);
//			return num.intValue();
//		} catch (SQLException e) 
//		{
//			throw new RuntimeException(e);
//		}
//	}
//
//	public User findByLastTime(String lasttime) {
//		try 
//		{
//			String sql = "select * from user where lastupdatetime=?";
//			
//			User user = qr.query(sql, new BeanHandler<User>(User.class), lasttime);
//			return user;
//		} catch (SQLException e) 
//		{
//			throw new RuntimeException(e);
//		}
//	}
//
//	public void updateOnline(int isonline ,int id) {
//		try 
//		{
//			Connection conn = dataSource.getConnection();
//			String sql = "update user set isonline=? where id=?";
//			qr.update(conn, sql, isonline,id);
//			
//		} catch (SQLException e) 
//		{
//			throw new RuntimeException(e);
//		}
//	}
//
	public List<User> findByisOnline() {
		try 
		{
			String sql = "select * from user where isonline=?";
			
			List<User> userList = qr.query(sql, new BeanListHandler<User>(User.class), 1);
			return userList;
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}

	public void updateIsOnline(int id,int isonline) {
		try 
		{
			Connection conn = dataSource.getConnection();
			String sql = "update user set isonline=? where id=?";
			qr.update(conn, sql, isonline,id);
			
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}

	public void updatelast(User user,String strDate) {
		// TODO Auto-generated method stub
		try 
		{
			Connection conn = dataSource.getConnection();
			String sql = "update user set lastupdatetime=? where id=?";
			qr.update(conn, sql, strDate,user.getId());
			
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}

	public int findNumber() {
		try 
		{
			String sql = "select count(*) from user where isonline=?";
			
			Number num  = (Number)qr.query(sql, new ScalarHandler(), 1);
			return num.intValue();
		} catch (SQLException e) 
		{
			throw new RuntimeException(e);
		}
	}
}
